/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/

/*****************************************************
PROJECT: BRIDGE C2 
PILOT: SCRAP
Date: 

DESCRIPTION: Use ASCEND approach to compare CRC, CRC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (this file): 
	CRC status at Index
	Imaging screen prior to Index
	Years of Observation after first Due date
	Ever due during observation period	

INPUT DATA:
SCRAP.CRC_patient_covariates
SCRAP.CRC_screen_dates;

OUTPUT DATA:
SCRAP.CRC_status_at_index



SECTIONS:
A. Status at index & Variable Creation 

****************************************************/
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
/****************************************************/

/***************************************************************/
/*A.Status at index & Variable Creation ;*/
/***************************************************************/

/*pull screens prior to index*/
proc sql;
create table screens_pre as
select p.raw_patid,
p.index_visit,
p.observation_end,
p.years_observation,
p.sdh_food,
p.sdh_housing,
p.sdh_transportation,
s.screen,
s.screen_date,
s.next_due2
from scrap.CRC_patient_covariates p left join 
scrap.CRC_screen_dates s
on p.raw_patid=s.raw_patid
and s.screen_date<p.index_visit
order by p.raw_patid, next_due2;

data last_screen_prior;
set screens_pre;
by raw_patid;
if last.raw_patid then output;
run;

/*generate analysis variables*/
data last_screen_prior ;
set last_screen_prior;
/*	CRC status at Index*/
last_screen_date_prior=screen_date;
CRC_status_index=next_due2>index_visit;
if next_due2>index_visit then date_due_at_index=next_due2;
if next_due2<=index_visit then date_due_at_index=index_visit;
/*	Years of Observation after first Due date*/
if next_due2^=. then obs_after_due=max(0,round(yrdif(next_due2,observation_end,'age'),0.01));
if next_due2=. then obs_after_due=years_observation;
/*	Ever due during observation period*/
ever_due=obs_after_due>0;
run;

proc sql;
create table imaging as
select distinct raw_patid,
max(screen_date) as last_image
from screens_pre
where screen^in('FIT','FOBT')
group by raw_patid;

proc sql;
create table last_screen_prior2 as
select p.*,
i.last_image^=. as imaging_prior
from last_screen_prior p
left join imaging i
on p.raw_patid=i.raw_patid;


data SCRAP.CRC_status_at_index
(keep=raw_patid sdh_food sdh_housing SDH_Transportation
index_visit observation_end
last_screen_date_prior
date_due_at_index
obs_after_due
ever_due status_at_index
imaging_prior);
set last_screen_prior2;
format date_due_at_index date9.;
if date_due_at_index>index_visit then status_at_index='Screened Prior';
if date_due_at_index<=index_visit then status_at_index='Due';
;
run;
